#coding:utf-8
import os
import re
import pymssql
from datetime import datetime, timedelta
import json
from email.mime.application import MIMEApplication
from email import MIMEText
import email
from email.mime.multipart import MIMEMultipart
import smtplib
import xlsxwriter
import sys
from sys import argv
import logging
reload(sys)
sys.setdefaultencoding('utf8')


'''
功能介绍：
1.实现发送邮件的功能
2.实现多附件发送
4.实现可配置数据源、主题、文本信息
5.excel 文件的格式，包括文件中表头字体大小、颜色、背景色、列宽
6.实现多 sheet

json 的配置模型如下：

{ "title":"邮件主题"
 ,"from":"发送人账户"
 ,"pwd":""
 ,"context":{
      "txt":"Hi you:\n #date# 的数据已经发送给您了。"
      ,"substitute":[
           "date":"last_date"
      ]
   }
 ,"attachement":[
     {
       "file_name":"#date# 的数据"
       ,"sheet":{
             "datesource":{
                "sql":""
                ,"parameters":{
                    "customer":""
                    ,"begin_date":"${lastday}"
                    ,"end_date":"${lastday}"
                }

             }
            ,"header":{
            "style":{
                "bg_color":""
                ,"colume_width":[
                {"range":""
                 ,"color":""
                 ,"width":""}
                ]
            }
            ,"title":[]
            }
            ,"sheet_name":""
       }
     }
 ]
 }

'''

class SendEmail:
    def __init__(self,config):
        self.config = config
        # day properties
        self.day = str(datetime.now() - timedelta(days=0)).replace('-', '').split()[0]
        self.lastday =str(datetime.now() - timedelta(days=1)).replace('-', '').split()[0]
        self.msg = MIMEMultipart()

    def genExcel(self,excel):
        #开始附件
        excelname = (str(excel["attachefolder"]) + str(excel["filename"]).replace("#day#", self.day).replace("#lastday#", self.lastday))
        for x in re.findall("\${day[-|+]\d+}",excelname,re.S):
            excelname = excelname.replace(x,str(datetime.now() - timedelta(days=int("".join(re.findall("[-|+]\d+",x,re.S))))).replace('-', '').split()[0])

        self.workbook = xlsxwriter.Workbook(excelname.encode("gbk"))
        for a in excel["sheet"]:
            conn = pymssql.connect(host=str(a["datasource"]["host"])
                                   , user=str(a["datasource"]["user"])
                                   , password=str(a["datasource"]["pwd"])
                                   , database=str(a["datasource"]["db"])
                                   , charset="utf8")
            cur = conn.cursor()
            if not cur:
                print "连接数据库失败"
            else:

                header_style = self.workbook.add_format({'bold': True})
                header_style.set_bg_color(str(a["header"]["style"]["bg_color"]))
                header_style.set_align('center')
                header_style.set_color("white")
                worksheet = self.workbook.add_worksheet(str(a["sheetname"]))
                for column_style in a["header"]["style"]["columnwidth"]:
                    worksheet.set_column(column_style["range"],int(column_style["width"]))

                for x in range(len(a["header"]["title"])):
                    worksheet.write(0, x, a["header"]["title"][x], header_style)

                # 开始写数据了
                sql_txt = a["datasource"]["sql"]
                if str(a["datasource"]["sql"]).endswith(".sql"):
                    sql_file = open(a["datasource"]["sql"])
                    sql_txt = str(sql_file.read())
                    sql_file.close()

                for replacestr in a["datasource"]["parameters"]:
                    sql_txt = sql_txt.replace(replacestr["name"],
                                    str(replacestr["value"]).replace('${day}',self.day).replace("${lastday}",self.lastday))
                    for x in re.findall("\${day[-|+]\d+}",sql_txt,re.S):
                        sql_txt = sql_txt.replace(x,str(datetime.now() - timedelta(days=int("".join(re.findall("[-|+]\d+",x,re.S))))).replace('-', '').split()[0])

                #print sql_txt
                cur.execute(sql_txt)
                row_index = 1
                for i in cur:
                    col_index = 0
                    for ii in i:
                        worksheet.write(row_index,col_index,ii)
                        col_index += 1
                    row_index += 1

                # 添加附件到邮件

        self.workbook.close()
        conn.close()
        attachefilename =(str(excel["attachefolder"]) + str(excel["filename"]).replace("#day#",self.day).replace("#lastday#",self.lastday))
        for x in re.findall("\${day[-|+]\d+}",attachefilename,re.S):
            attachefilename = excelname.replace(x,str(datetime.now() - timedelta(days=int("".join(re.findall("[-|+]\d+",x,re.S))))).replace('-', '').split()[0])

        return attachefilename

    def getAttachement(self):
        file = open(self.config)
        json_config = json.load(file)

        for a in json_config["attachement"]:
            contype = 'application/octet-stream'
            maintype, subtype = contype.split('/', 1)
            file_name = self.genExcel(a).encode('gbk')
            file_data = open(file_name,'rb')
            file_msg = email.MIMEBase.MIMEBase(maintype, subtype)
            file_msg.set_payload(file_data.read())
            file_data.close( )
            email.Encoders.encode_base64(file_msg)
            basename = os.path.basename(file_name)
            file_msg.add_header('Content-Disposition', 'attachment', filename = basename)
            self.msg.attach(file_msg)
        file.close()

    def sendSome(self):
        file = open(self.config)
        json_config = json.load(file)
        title = str(json_config["title"]).replace("#day#",self.day).replace("lastday",self.lastday)
        for x in re.findall("\${day[-|+]\d+}",title,re.S):
            title = title.replace(x,str(datetime.now() - timedelta(days=int("".join(re.findall("[-|+]\d+",x,re.S))))).replace('-', '').split()[0])

        #接收者
        self.msg['to'] = json_config["to"]
        self.msg['from'] = str(json_config["from"])
        self.msg['Cc'] = str(json_config["Cc"])
        self.msg['Subject'] = title
        txt = str(json_config["content"]["txt"]).replace("#day#",str(self.day)).replace("#lastday#",self.lastday)
        for x in re.findall("\${day[-|+]\d+}",txt,re.S):
           txt = txt.replace(x,str(datetime.now() - timedelta(days=int("".join(re.findall("[-|+]\d+",x,re.S))))).replace('-', '').split()[0])

        text_msg = MIMEText.MIMEText(txt, _charset='utf-8')
        self.getAttachement()
        self.msg.attach(text_msg)
        file.close()
        try:
            server = smtplib.SMTP()
            server.connect('smtp.exmail.qq.com')
            #邮箱账户和密码
            server.login(str(json_config["from"]), str(json_config["pwd"]))
            server.sendmail(self.msg['from'], self.msg['to'].split(',')+self.msg['Cc'].split(','),self.msg.as_string())
            print "email sended"
            server.quit()
        except Exception, e:
            print str(e)
if __name__ == '__main__':
    ss = SendEmail(argv[1])
    ss.sendSome()


